﻿using System.Data;
using System.Data.SqlClient;
using Dapper;
using MySql.Data.MySqlClient;

namespace Neoit.Tools.DBHelper
{
    public class GenerateInsert
    {
        private readonly string _connectionString;
        private DBType _dbType;

        public GenerateInsert(string connectionString)
        {
            _connectionString = connectionString;
        }

        public List<string> Run(string tableName, string filterCondition)
        {
            List<string> insertStatements = new List<string>();

            using (IDbConnection connection = CreateConnection())
            {
                connection.Open();
                string query = $"SELECT * FROM {tableName} WHERE {filterCondition}";
                var rows = connection.Query<dynamic>(query);
                var identity = GetIdentity(connection, _dbType, tableName);
                foreach (var row in rows)
                {
                    var columns = new List<string>();
                    var values = new List<string>();

                    foreach (var property in (IDictionary<string, object>)row)
                    {
                        if (property.Key == identity) continue;
                        columns.Add(property.Key);

                        if (property.Value == null)
                        {
                            values.Add("NULL");
                        }
                        else if (property.Value is string || property.Value is DateTime)
                        {
                            values.Add($"'{property.Value}'");
                        }
                        else if (property.Value is bool)
                        {
                            var _value = Convert.ToBoolean(property.Value) ? 1 : 0;
                            values.Add($"{_value}");
                        }
                        else
                        {
                            values.Add(property.Value.ToString());
                        }
                    }
                    string insertStatement = $"INSERT INTO {tableName} ({string.Join(", ", columns)}) VALUES ({string.Join(", ", values)});";
                    insertStatements.Add(insertStatement);
                }
            }
            return insertStatements;
        }

        private IDbConnection CreateConnection()
        {
            // 根据连接字符串的类型创建数据库连接
            if (_connectionString.Contains("Server="))
            {
                _dbType = DBType.Sqlserver;
                return new SqlConnection(_connectionString); // SQL Server
            }
            else if (_connectionString.Contains("Database="))
            {
                _dbType = DBType.Mysql;
                return new MySqlConnection(_connectionString); // MySQL
            }
            throw new Exception("Unsupported database type.");
        }

        //identity标识的字段，无法主动插入
        private string GetIdentity(IDbConnection dbConnection, DBType dBType, string tableName)
        {
            string name = string.Empty;
            switch (dBType)
            {
                case DBType.Mysql:
                    break;
                case DBType.Sqlserver:
                    name = dbConnection.Query<string>("SELECT COLUMN_NAME FROM  INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test'\r\nAND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1").FirstOrDefault();
                    break;
                default:
                    break;
            }
            return name;
        }
    }


    public enum DBType
    {
        Mysql,
        Sqlserver
    }
}
